package com.xiangty.reader;

import com.xiangty.bean.CustTemp;
import org.springframework.batch.core.configuration.annotation.StepScope;
import org.springframework.batch.item.database.JdbcPagingItemReader;
import org.springframework.batch.item.database.Order;
import org.springframework.batch.item.database.support.MySqlPagingQueryProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class JdbcPagingItemReaderConfiguration {

    @Autowired
    private DataSource dataSource;

    @Bean("custTempItemReader2")
    @StepScope
    public JdbcPagingItemReader<CustTemp> custTempItemReader(
            @Value("#{stepExecutionContext[fromId]}") final String fromId,
            @Value("#{stepExecutionContext[toId]}") final String toId,
            @Value("#{stepExecutionContext[range]}") final String range
    ) {

        System.out.println("------------JdbcPagingItemReader开始-------------【" + Thread.currentThread().getName() + "】--fromId: " + fromId + "  -----toId:" + toId);

        JdbcPagingItemReader<CustTemp> reader = new JdbcPagingItemReader<>();
        reader.setDataSource(dataSource);

//        int pageSize = range == null ? 1000 : Integer.parseInt(range) + 1000; // 默认的1000等于步骤的chunk参数值
        int pageSize = 1000;
        reader.setFetchSize(pageSize); // 一次取多少数据
        reader.setPageSize(pageSize);

        // 把读取到的记录转换成CustTemp对象
        reader.setRowMapper(new RowMapper<CustTemp>() {
            @Override
            public CustTemp mapRow(ResultSet rs, int rowNum) throws SQLException {
                CustTemp custTemp = new CustTemp();
                custTemp.setId(rs.getInt(1));
                custTemp.setCustNo(rs.getString(2));
                custTemp.setApplicationNo(rs.getString(3));
                custTemp.setValue1(rs.getString(4));
                custTemp.setValue2(rs.getString(5));
                custTemp.setValue3(rs.getString(6));
                custTemp.setValue4(rs.getString(7));
                custTemp.setValue5(rs.getString(8));
                custTemp.setValue6(rs.getString(9));
                custTemp.setValue7(rs.getString(10));
                custTemp.setValue8(rs.getString(11));
                custTemp.setValue9(rs.getString(12));
                custTemp.setValue10(rs.getString(13));
                custTemp.setValue11(rs.getString(14));
                custTemp.setValue12(rs.getString(15));
                custTemp.setValue13(rs.getString(16));
                custTemp.setValue14(rs.getString(17));
                custTemp.setValue15(rs.getString(18));
                custTemp.setValue16(rs.getString(19));
                custTemp.setValue17(rs.getString(20));
                custTemp.setValue18(rs.getString(21));
                custTemp.setValue19(rs.getString(22));
                custTemp.setValue20(rs.getString(23));
                return custTemp;
            }
        });
        // 指定SQL语句
        MySqlPagingQueryProvider provider = new MySqlPagingQueryProvider();
        provider.setSelectClause("id,custno,applicationno,value1,value2,value3,value4,value5,value6,value7,value8,value9,value10,value11,value12,value13,value14,value15,value16,value17,value18,value19,value20");
        provider.setFromClause(" t_cust_temp ");
        provider.setWhereClause(" id BETWEEN " + Integer.valueOf(fromId) + " and " + Integer.valueOf(toId));
//        provider.setWhereClause(" id BETWEEN " + Integer.valueOf(fromId) + " and " + Integer.valueOf(toId) + " and id<=:id"); // 带参数的写法

        // 指定根据那个字段排序
        Map<String, Order> sort = new HashMap<>();
        sort.put("id", Order.ASCENDING);
        provider.setSortKeys(sort);

//        // 带参数的写法
//        Map<String, Object> parameterValues = new HashMap<>();
//        parameterValues.put("id", toId);
//        reader.setParameterValues(parameterValues);

        reader.setQueryProvider(provider);

        System.out.println("------------JdbcPagingItemReader结束-------------【" + Thread.currentThread().getName() + "】");
        return reader;
    }

}
